package com.wuseguang.springcloud.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
import java.util.Map;

public class AttendExcelUtils {
    private static Logger logger = LoggerFactory.getLogger(AttendExcelUtils.class);

    public static void downexcle(HttpServletResponse response, List<Map<String, Object>> mapList,
                                 List<String> weeklist, String filename)
            throws IOException {

        logger.info("downloadAttend_ExcelUtil downexcle.....");

        ByteArrayOutputStream os = new ByteArrayOutputStream();

        createWorkBook(mapList, weeklist, filename).write(os);

        logger.info("begin downloadExcel");

        downloadExcel(response, os, filename);

        logger.info("end downloadExcel");

    }

    public static Workbook createWorkBook(List<Map<String, Object>> list, List<String> weeklist, String filename) {
        logger.info("createWorkBook(),list={}", list.toString());

        // 创建excel工作簿
        Workbook wb = new HSSFWorkbook();

        logger.info("sheetname=" + filename);

        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(filename);

        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for (int i = 0; i < weeklist.size(); i++) {
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
        }

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 12);
        f.setColor(IndexedColors.RED.getIndex());
        f.setFontName("宋体");

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 12);
        f2.setColor(IndexedColors.BLACK.getIndex());
        f2.setFontName("宋体");

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);


        logger.info("creating first row");
        // 创建第一行
        Row row = sheet.createRow((short) 0);
        row.setHeightInPoints(20);

        logger.info("create header weeklist={}", weeklist.toString());
        // 设置列名
        for (int i = 0; i < weeklist.size(); i++) {
            String s = weeklist.get(i);
            Cell cell = row.createCell(i);
            cell.setCellValue(s);
            cell.setCellStyle(cs);
            logger.info("set column={}, value={}", i, s);
        }


        // 设置每行每列的值
        if (!CollectionUtils.isEmpty(list)) {
            logger.info("list is not null,size=" + list.size());
            for (short i = 1; i <= list.size(); i++) {
                logger.info("create row = {}  / ", i, list.size());
                // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
                // 创建一行，在页sheet上
                Row row1 = sheet.createRow((short) i);
                row1.setHeightInPoints(20);
                // 在row行上创建一个方格
                logger.info("create cell,weeklist.length=", weeklist.size());
                Map<String, Object> stringObjectMap = list.get(i - 1);
                String keys[] = {"name", "cname"};
                int length = keys.length;
                for (short j = 0; j < length; j++) {
                    logger.info("create cell " + j);
                    Cell cell = row1.createCell(j);
                    // 判断是否为时间格式，如果为时间格式，要进行格式转换
                    String valueString = "";
                    Object bufferValue = stringObjectMap.get(keys[j]);
                    logger.info("cell[" + j + "]=" + bufferValue);
                    valueString = bufferValue == null ? "" : bufferValue.toString();
                    cell.setCellValue(valueString);
                    cell.setCellStyle(cs2);
                }
                List<String> attendlist = (List<String>) stringObjectMap.get("attendlist");
                for (int j = 0; j < attendlist.size(); j++) {
                    logger.info("create cell ", j + length);
                    Cell cell = row1.createCell(j + length);
                    // 判断是否为时间格式，如果为时间格式，要进行格式转换
                    String valueString = "";
                    Object bufferValue = attendlist.get(j);
                    logger.info("cell[" + j + "]=" + bufferValue);
                    valueString = bufferValue == null ? "" : bufferValue.toString();
                    cell.setCellValue(valueString);
                    cell.setCellStyle(cs2);
                }
                logger.info("write row finished,rowno=" + i);
            }
        } else {
            logger.info("list is null,not append data to excel");
        }

        logger.info(wb.toString());
        return wb;
    }


    public static void downloadExcel(HttpServletResponse response, ByteArrayOutputStream os, String filename)
            throws IOException {

        logger.info("downloadExcel, filename=" + filename);

        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);

        // 设置response参数，可以打开下载页面
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + new String((filename + ".xls").getBytes(), "iso-8859-1"));
        /*response.setContentType("application/octet-stream");*/
        response.setHeader("Access-Control-Allow-Origin", "*");
        response.setHeader("Access-Control-Allow-Credentials", "true");
        response.setHeader("Access-Control-Allow-Methods", "GET,POST,PUT,DELETE,OPTIONS");
        ServletOutputStream out = response.getOutputStream();

        try {
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(out);
            IOUtils.copy(bis, bos);
            bos.flush();

            //logger.info("content, byte size()=" +content.length+",content:" + new String(content, "utf-8"));

        } catch (Exception ex) {
            logger.error("export to excel failed,filename=" + filename, ex);
        }
    }
}
